package com.plentitude.easytrader.provider;

import com.plentitude.easytrader.io.DataHandler;
import com.plentitude.easytrader.provider.WatchlistContract.DataColumns;
import com.plentitude.easytrader.provider.WatchlistContract.QuotesColumns;
import android.app.SearchManager;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;


/**
 * Helper for managing {@link SQLiteDatabase} that stores data for
 * {@link WatchlistProvider}.
 *
 * @author team@getstarted.com.ua
 */
public class WatchlistDatabase extends SQLiteOpenHelper {
    private static final String TAG = "WatchlistDatabase";
    // NOTE: carefully update onUpgrade() when bumping database versions to make
    // sure user data is saved.
    // released version: 3
    private static final int DATABASE_VERSION = 3;
    private DataHandler mDataHandler;
    private Context context;
    private static final String T = " TEXT,";
    private static final String F = " FLOAT,";
    

    public interface Tables {
        String DATA = "data";
        String QUOTES = "quotes";
        String SEARCH_SUGGEST = "search_suggest";
    }

    public WatchlistDatabase(Context context, String dbName) {
        super(context, dbName, null, DATABASE_VERSION);
        this.context = context;
    }
    
    @Override
    public void onCreate(SQLiteDatabase db) {
    	StringBuilder sql = new StringBuilder(1024);
		sql.append("CREATE TABLE ").append(Tables.DATA).append(" (")
            .append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT,")
    		.append(DataColumns.STRATEGY).append(T)
            .append(DataColumns.TYPE).append(T)
            .append(DataColumns.SETUP).append(T)
            .append(DataColumns.EPIC).append(T)
            .append(DataColumns.NAME).append(T)
            .append(DataColumns.SECTOR).append(T)
            .append(DataColumns.LOW_PE_VS_SECTOR).append(T)
            .append(DataColumns.LOW_OP_MARGIN).append(T)
            .append(DataColumns.PEG).append(T)
            .append(DataColumns.EARNINGS_GROWS).append(T)
            .append(DataColumns.REVENUE_GROWTH).append(T)
            .append(DataColumns.INTEREST_COVER).append(T)
            .append(DataColumns.NET_CURRENT_ASSETS).append(T)
            .append(DataColumns.NET_GEARING).append(T)
            .append(DataColumns.PROJECTED_EARNINGS).append(T)
            .append(DataColumns.HIGH_PE_VS_SECTOR).append(T)
            .append(DataColumns.NEG_CURRENT_ASSETS).append(T)
            .append(DataColumns.DECREASING_REVENUES).append(T)
            .append(DataColumns.HIGH_OP_MARGIN).append(T)
            .append(DataColumns.FUNDAMENTAL_SCORE).append(T)
            .append(DataColumns.MARKET).append(T)
            .append(DataColumns.SECTOR_PERF).append(T)
            .append(DataColumns.SECTOR_SCORE).append(T)
            .append(DataColumns.LONG_TRADE_SCORE).append(T)
            .append(DataColumns.SHORT_TRADE_SCORE).append(T)
            .append(DataColumns._RSI_BUY).append(T)
            .append(DataColumns._MACD_BUY).append(T)
            .append(DataColumns._STOCH_BUY).append(T)
            .append(DataColumns._EMA_BUY).append(T)
            .append(DataColumns._RSI_SELL).append(T)
            .append(DataColumns._MACD_SELL).append(T)
            .append(DataColumns._STOCH_SELL).append(T)
            .append(DataColumns._EMA_SELL).append(T)
            .append(DataColumns.BUY_INDICATOR_NUM).append(T)
            .append(DataColumns.SELL_INDICATOR_NUM).append(T)
            .append(DataColumns.BROKER_CONSENSUS).append(T)
            .append(DataColumns.LTBAT_CONSENSUS).append(T)
            .append(DataColumns.ADDITIONAL_INFO).append(T)
            .append(DataColumns.NEXT_SIG_STRENGTH).append(T)
            .append(DataColumns.NEXT_SIG_DESC).append(T)
            .append(DataColumns.REC_ENTRY_LEVEL).append(T)
            .append(DataColumns.REC_STOP_LEVEL).append(T)
            .append(DataColumns.TARGET_LEVEL).append(T)
            .append(DataColumns.REWARD_RATIO).append(T)
            .append(DataColumns.URL_CHART).append(T)
            .append(DataColumns.URL_SECTOR_CHART).append(T)
            .append(DataColumns.URL_EOD_CHART).append(T)
            .append(DataColumns.URL_LIVE_CHART).append(T)
            .append(DataColumns.URL_SUMMARY).append(T)
            .append(DataColumns.ACTIVE_SECTOR).append(T)
            .append(DataColumns.VOLUME_EOD).append(T)
            .append(DataColumns.COMPANY_PROFILE).append(T)
            .append(DataColumns.ALERTS).append(T)
            .append(DataColumns.NEWS).append(T)
            .append(DataColumns.OUTLOOK).append(T)
            .append(DataColumns.PARAMS_AND_SETTINGS).append(T)
            .append(DataColumns.DEFAULT_FIELD_VALUES).append(T)
            .append("UNIQUE (").append(DataColumns.EPIC).append(") ON CONFLICT REPLACE)");
    	db.execSQL(sql.toString());
    	
    	sql.delete(0, sql.length());
    	sql.append("CREATE TABLE ").append(Tables.QUOTES).append(" (")
        	.append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT,")
        	.append(QuotesColumns.SYMBOL).append(T)
            .append(QuotesColumns.PRICE).append(F)
            .append(QuotesColumns.DATE).append(T)
            .append(QuotesColumns.TIME).append(T)
            .append(QuotesColumns.CHANGE).append(T)
            .append(QuotesColumns.OPEN).append(F)
            .append(QuotesColumns.HIGH).append(F)
            .append(QuotesColumns.LOW).append(F)
            .append(QuotesColumns.VOLUME).append(F)
            .append("UNIQUE (").append(QuotesColumns.SYMBOL).append(") ON CONFLICT REPLACE)");
        db.execSQL(sql.toString());
        
    	sql.delete(0, sql.length());
    	sql.append("CREATE TABLE ").append(Tables.SEARCH_SUGGEST).append(" (")
        	.append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT,")
        	.append(SearchManager.SUGGEST_COLUMN_TEXT_1).append(" TEXT NOT NULL,")
        	.append("UNIQUE (").append(SearchManager.SUGGEST_COLUMN_TEXT_1).append(") ON CONFLICT REPLACE)");
        db.execSQL(sql.toString());

        // Insert initial data
        mDataHandler = new DataHandler(context, db);
        
        mDataHandler.insertData();
        mDataHandler.insertQuotes();

        db.execSQL("CREATE INDEX data_epic_idx ON data(epic)");
        db.execSQL("CREATE INDEX quotes_symbol_idx ON quotes(symbol)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG, "onUpgrade() from " + oldVersion + " to " + newVersion);
        if(oldVersion != DATABASE_VERSION) {
            db.execSQL("DROP TABLE IF EXISTS " + Tables.DATA);
            db.execSQL("DROP TABLE IF EXISTS " + Tables.QUOTES);
            db.execSQL("DROP TABLE IF EXISTS " + Tables.SEARCH_SUGGEST);
            db.execSQL("DROP INDEX IF EXISTS data_epic_idx");
            db.execSQL("DROP INDEX IF EXISTS quotes_symbol_idx");
            onCreate(db);
        }
    }

}
